﻿using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Openfree.Framework.DataAccess;

public partial class PMSWeb_CostReport : PMSBase
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string cmdText = @"select a.*,
                (select count(*) from PMS_CostBillItem where BillID=a.BillID) UserCount,
                (select sum(PayMoney) from PMS_CostBillItem where BillID=a.BillID and PayMoney>=0  and IsSelected=1) ShouFei,
                (select sum(PayMoney) from PMS_CostBillItem where BillID=a.BillID and PayMoney<0  and IsSelected=1) QianFei
                from PMS_CostBill a order by BillDate desc";
            FillGirdView(0, cmdText);
        }
        else
        {
            FillGirdView(this.gridView.SplitPageIndex, string.Empty);
        }
    }
   
    protected void FillGirdView(int currentPage, string cmdText)
    {
        if (string.IsNullOrEmpty(cmdText))
        {
            cmdText = ViewState[this.ClientID + "cmdText"].ToString();
        }
        SqlSplitPage splitPage = new SqlSplitPage(base.Connection, cmdText);
        splitPage.PageSize = 10;
        DataSet ds = splitPage.LoadDataSet(currentPage);
        //成功查询出数据后缓存
        ViewState[this.ClientID + "cmdText"] = cmdText;
        gridView.ShowFooter = true;
        gridView.NavigationData = splitPage.GetNavigationData(currentPage);
        gridView.DataSource = ds.Tables[0];
        gridView.DataBind();
    }

    protected void gridView_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        
    }

    protected void gridView_SplitPageIndexChange(object sender, CommandEventArgs e)
    {
        FillGirdView(Int32.Parse(e.CommandArgument.ToString()), ViewState[this.ClientID + "cmdText"].ToString());
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        StringBuilder cmdText = new StringBuilder(@"select a.*,
                (select count(*) from PMS_CostBillItem where BillID=a.BillID) UserCount,
                (select sum(PayMoney) from PMS_CostBillItem where BillID=a.BillID and PayMoney>=0  and IsSelected=1) ShouFei,
                (select sum(PayMoney) from PMS_CostBillItem where BillID=a.BillID and PayMoney<0  and IsSelected=1) QianFei
                from PMS_CostBill a where 1=1 ");

        if (tbSubject.Text.Trim() != string.Empty)
        {
            cmdText.Append("and Subject like '%" + tbSubject.Text.Trim().Replace("'","")+"%' ");
        }
        if (!string.IsNullOrEmpty(tbBeginDate.Text.Trim()))
        {
            cmdText.Append(" and a.CreateDate >= CONVERT(varchar, '" + tbBeginDate.Text.Trim() + "', 120 ) ");
        }
        if (!string.IsNullOrEmpty(tbEndDate.Text.Trim()))
        {
            cmdText.Append(" and a.CreateDate <= CONVERT(varchar, '" + tbEndDate.Text.Trim() + "', 120 ) ");
        } 
        cmdText.Append(" order by BillDate desc");

        FillGirdView(0, cmdText.ToString());
    }

    protected void btnExportSF_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Request["BillID"]))
        {
            base.Alert("请选择一条记录");
            FillGirdView(this.gridView.SplitPageIndex, string.Empty);
            return;
        }
        ExportData(">=");
        
    }

    protected void btnExportQF_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Request["BillID"]))
        {
            base.Alert("请选择一条记录");
            FillGirdView(this.gridView.SplitPageIndex, string.Empty);
            return;
        }
        ExportData("<");
    }


    private void ExportData(string sq)
    {
        //动态生成表格各列
        string cmdText = @"select a.* from(
                    select distinct CostID from PMS_CostBillItem) a 
                    left join PMS_CostItem b on a.CostID=b.CostID order by b.OrderIndex";
        string column = string.Empty;
        DataSet dsCostItem = BaseAccess.GetDataSet(base.Connection, cmdText);
        foreach (DataRow row in dsCostItem.Tables[0].Rows)
        {
            //前缀防止与 PMS_Customer 表默认字段冲突
            column += ", '" + row["CostID"].ToString() + "' Col_" + row["CostID"].ToString();
        }
        cmdText = @"select a.*, 
                (select top 1 Telephone from PMS_CostBillItem where a.HouseNo=HouseNo and a.RoomNo =RoomNo ) Telephone {0} 
                from ( select HouseNo, RoomNo from PMS_CostBillItem where BillID='{1}' and PayMoney{2}0 group by HouseNo, RoomNo ) a";
        cmdText = string.Format(cmdText, column, Request["BillID"], sq);

        //生成导出表格结构
        DataSet dsReport = BaseAccess.GetDataSet(base.Connection, cmdText);

        //取出数据，填充数据使用
        cmdText = "select * from PMS_CostBillItem where BillID='{0}' and PayMoney{1}0";
        cmdText = string.Format(cmdText, Request["BillID"], sq);
        DataSet dsCostBillItem = BaseAccess.GetDataSet(base.Connection, cmdText);

        DataColumnCollection cols = dsReport.Tables[0].Columns;
        double value = 0;
        foreach (DataRow row in dsReport.Tables[0].Rows)
        {

            for (int i = 3; i < cols.Count; i++)
            {
                string costID = cols[i].ColumnName.Replace("Col_", "");
                cmdText = "HouseNo='{0}' and RoomNo='{1}' and CostID='{2}' and IsSelected=1";
                cmdText = string.Format(cmdText, row[0].ToString(), row[1].ToString(), costID);
                DataRow[] rows = dsCostBillItem.Tables[0].Select(cmdText);

                if (rows.Length == 0)
                {
                    row[cols[i].ColumnName] = DBNull.Value;
                    continue;
                }
                if (!string.IsNullOrEmpty(rows[0]["PayMoney"].ToString()))
                {
                    row[cols[i].ColumnName] = DBNull.Value;
                    if (double.TryParse(rows[0]["PayMoney"].ToString(), out value))
                    {
                        row[cols[i].ColumnName] = value;
                    }

                }
                else
                {
                    row[cols[i].ColumnName] = DBNull.Value;
                }
            }
        }


        cmdText = "select CostID, Subject from PMS_CostItem";
        dsCostItem = BaseAccess.GetDataSet(base.Connection, cmdText);
        cols[0].ColumnName = "楼号区域";
        cols[1].ColumnName = "房间号";
        cols[2].ColumnName = "联系电话";
        for (int i = 3; i < cols.Count; i++)
        {
            string costID = cols[i].ColumnName.Replace("Col_", "");
            DataRow[] rows = dsCostItem.Tables[0].Select("CostID='" + costID + "'");
            if(rows.Length>0)
            {
                cols[i].ColumnName = rows[0]["Subject"].ToString();
            }
        }

        string fileName = Guid.NewGuid().ToString()+".csv";
        Openfree.Framework.Common.Convert.DataTableToCSV(dsReport.Tables[0],
            Page.MapPath(SessionManager.TempVirtualFolder) + fileName);

        Response.Redirect(SessionManager.TempVirtualFolder + fileName);
    }
}
